IF NOT EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE [name] = N'student') 
	BEGIN
	Create TABLE [dbo].student (
		[Id] int identity(1,1) not null,
		[name] varchar(100) NOT NULL,
		[age] int,
		[birth] datetime ,
		CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED ( [Id] )
	)
	END
GO


IF NOT EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE [name] = N'role') 
	BEGIN
	Create TABLE [dbo].role (
		[Id] int identity(1,1) not null,
		[RoleName] varchar(100) NOT NULL,
		CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED ( [Id] )
	)
	END
GO
If not exists(Select 1 From role Where Id = -1)
Begin
	SET IDENTITY_INSERT role ON
	Insert into role(Id, roleName) Values(-1, '系统管理员')
	SET IDENTITY_INSERT role OFF
End
Go



IF NOT EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE [name] = N'rolepermission') 
	BEGIN
	Create TABLE [dbo].rolepermission (
		[Id] int identity(1,1) not null,
		[RoleId] int NOT NULL,
		[PermissionId] int NOT NULL,
		CONSTRAINT [PK_Rolepermission] PRIMARY KEY CLUSTERED ( [Id] )
	)
	END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_Rolepermission_RoleId')
BEGIN
   CREATE NONCLUSTERED INDEX IX_Rolepermission_RoleId ON rolepermission (RoleId)
END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_Rolepermission_PermissionId')
BEGIN
   CREATE NONCLUSTERED INDEX IX_Rolepermission_PermissionId ON rolepermission (PermissionId)
END
GO
If not exists(Select 1 From rolepermission Where Id = 1)
Begin
	SET IDENTITY_INSERT rolepermission ON
	INSERT INTO rolepermission(Id, RoleId, PermissionId) VALUES (1,-1,1);
	INSERT INTO rolepermission(Id, RoleId, PermissionId) VALUES (2,-1,2);	
	SET IDENTITY_INSERT rolepermission OFF
End
Go




IF NOT EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE [name] = N'user') 
	BEGIN
	Create TABLE [dbo].[user] (
		[Id] int identity(1,1) not null,
		[userName] varchar(100) not NULL,
		[pwd] varchar(100) DEFAULT NULL,
		[photo] varchar(100) DEFAULT NULL,
		[name] varchar(100) DEFAULT NULL,
		[wxuid] varchar(100) DEFAULT NULL,
		[phone] varchar(100) DEFAULT NULL,
		CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [Id] )
	)
	END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_User_userName')
BEGIN
   CREATE unique INDEX IX_User_userName ON [user] (userName)
END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_User_name')
BEGIN
   CREATE NONCLUSTERED INDEX IX_User_name ON [user] (name)
END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_User_phone')
BEGIN
   CREATE NONCLUSTERED INDEX IX_User_phone ON [user] (phone)
END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_User_wxuid')
BEGIN
   CREATE NONCLUSTERED INDEX IX_User_wxuid ON [user] (wxuid)
END
GO
If not exists(Select 1 From [user] Where Id = 1)
Begin
	SET IDENTITY_INSERT [user] ON
	INSERT INTO [user] (Id, userName, pwd, photo, name, wxuid, phone) VALUES (-1,'iraetaAdmin','admin123','https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif','管理员', '', '');
	SET IDENTITY_INSERT [user] OFF
End
Go


IF NOT EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE [name] = N'userrole') 
	BEGIN
	Create TABLE [dbo].userrole (
		[Id] int identity(1,1) not null,
		[UserId] int NOT NULL,
		[RoleId] int NOT NULL,
		CONSTRAINT [PK_userrole] PRIMARY KEY CLUSTERED ( [Id] )
	)
	END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_userrole_RoleId')
BEGIN
   CREATE NONCLUSTERED INDEX IX_userrole_RoleId ON userrole (RoleId)
END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_userrole_UserId')
BEGIN
   CREATE NONCLUSTERED INDEX IX_userrole_UserId ON userrole (UserId)
END
GO
If not exists(Select 1 From userrole Where Id = 1)
Begin
	SET IDENTITY_INSERT userrole ON
	INSERT INTO userrole(Id, UserId, RoleId) VALUES (1,-1,-1);
	SET IDENTITY_INSERT userrole OFF
End
Go



IF NOT EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE [name] = N'sys_files') 
	BEGIN
	Create TABLE [dbo].[sys_files] (
		[f_id] int identity(1,1) not null,
		[entityTypeId] int NOT NULL,
		[entityId] int NOT NULL,		
		[fileName] varchar(100) not NULL,
		[filePath] varchar(100) not NULL,
		[create_by] varchar(100) DEFAULT NULL,
		[create_time] datetime DEFAULT NULL,
		[update_by] varchar(100) DEFAULT NULL,
		[update_time] datetime DEFAULT NULL,
		CONSTRAINT [PK_sys_files] PRIMARY KEY CLUSTERED ( [Id] )
	)
	END
GO



IF NOT EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE [name] = N'sys_org') 
	BEGIN
	Create TABLE [dbo].[sys_org] (
		[Id] int identity(1,1) not null,
		[pid] int NOT NULL,
		[name] varchar(100) not NULL,
		[member] varchar(100) DEFAULT NULL,
		CONSTRAINT [PK_sys_org] PRIMARY KEY CLUSTERED ( [Id] )
	)
	END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_sys_org_pid')
BEGIN
   CREATE NONCLUSTERED INDEX IX_sys_org_pid ON sys_org (pid)
END
GO
If not exists(Select 1 From sys_org Where Id = 1)
Begin
	SET IDENTITY_INSERT sys_org ON
	INSERT INTO sys_org(Id, pid, name, member) VALUES (1, 0,'伊莱特能源装备', '1');
	SET IDENTITY_INSERT sys_org OFF
End
Go



IF NOT EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE [name] = N'sys_orguser') 
	BEGIN
	Create TABLE [dbo].sys_orguser (
		[Id] int identity(1,1) not null,
		[org_id] int NOT NULL,
		[u_id] int NOT NULL,
		CONSTRAINT [PK_orguser] PRIMARY KEY CLUSTERED ( [Id] )
	)
	END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_sys_orguser_org_id')
BEGIN
   CREATE NONCLUSTERED INDEX IX_sys_orguser_org_id ON sys_orguser (org_id)
END
GO
IF NOT EXISTS( SELECT 1 FROM sysindexes WHERE name=N'IX_sys_orguser_u_id')
BEGIN
   CREATE NONCLUSTERED INDEX IX_sys_orguser_u_id ON sys_orguser (u_id)
END
GO



If exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[getOrgChildrenIds]'))
begin
	drop function [dbo].getOrgChildrenIds
end
go
Create function [dbo].getOrgChildrenIds (@orgId int)
returns @table table(Id int)
as 
begin
	With zuZhiList as(
		Select Id From sys_org where Id = @orgId
		Union All
		Select o.Id From sys_org o Join zuZhiList on o.pid = zuZhiList.Id
	)

	insert into @table(Id) select distinct zl.Id from zuZhiList zl join sys_org o on zl.Id = o.Id
return 
end
GO


If exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[getOrgParentIds]'))
begin
	drop function [dbo].getOrgParentIds
end
go
Create function [dbo].getOrgParentIds (@orgId int)
returns varchar
as 
begin
	declare @oTemp varchar(255)
	SET @oTemp = '';
	select @oTemp = member from sys_org where Id = @orgId
	return @oTemp	 
end
GO


If exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].SplitStrToInt'))
Begin
	Drop function [dbo].[SplitStrToInt]
End
Go
Create function [dbo].[SplitStrToInt]
(
    @input varchar(4000)
)
Returns @result Table 
(
    id int
) 
AS
Begin
	Declare @seperator varchar(1)
	Set @seperator = ','
	declare @index int, @entry int
    set @index = charindex(@seperator, @input)

    while (@index>0)
    begin
		if @index > 1
		begin
			set @entry=ltrim(rtrim(substring(@input, 1, @index-1)))
			insert into @result values(Convert(int, @entry))
		end

        set @input = substring(@input, @index + 1, len(@input) - @index)
        set @index = charindex(@seperator, @input)
    end
    
	if @input != ''
	begin
		insert into @result values(Convert(int, @input))
	end
    return
End
Go